package xkp.lesson.dao;

import xkp.lesson.db.DBConnection;
import xkp.lesson.entity.JobInfoEntity;

import javax.xml.transform.Result;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class JobInfoDao  {
    public int getDataCount() throws SQLException {
        int dataCount = 0;
        Connection connection = DBConnection.getConnection();
        String sql = "select count(*) as dataCount from job_info";
        PreparedStatement statement = connection.prepareStatement(sql);
        ResultSet set = statement.executeQuery();
        set.next();
        dataCount = set.getInt("dataCount");
        set.close();
        statement.close();
        connection.close();
        return dataCount;
    }

    public List<JobInfoEntity> selectByPage(int pageNum,int pageSize,String searcKw,String searchCity) throws SQLException {
        List<JobInfoEntity> list = new ArrayList<>();
        Connection connection = DBConnection.getConnection();
        String sql = "CALL select_by_page(?,?,?,?)";
        CallableStatement statement = connection.prepareCall(sql);
        statement.setInt(1,pageNum);
        statement.setInt(2,pageSize);
        statement.setString(3,searcKw);
        statement.setString(4,searchCity);
        ResultSet set = statement.executeQuery();
        while(set.next()){
            String jobId = set.getString("job_id");
            String jobName = set.getString("job_name");
            String jobSalary = set.getString("job_salary");
            String jobEducation = set.getString("job_education");
            String jobExprience = set.getString("job_exprience");
            String jobArea = set.getString("job_area");
            String companyName = set.getString("company_Name");
            JobInfoEntity jobInfo = new JobInfoEntity(jobId,jobName,jobSalary,jobEducation,
                    jobExprience,jobArea,companyName);
            list.add(jobInfo);

        }
        set.close();
        statement.close();
        connection.close();
        return list;
    }

    public List<JobInfoEntity> selectAll() throws SQLException {
        List<JobInfoEntity> list = new ArrayList<>();
        Connection connection = DBConnection.getConnection();
        String sql = "select * from job_info";
        PreparedStatement statement = connection.prepareStatement(sql);
        ResultSet set = statement.executeQuery();
        while(set.next()){
            String jobId = set.getString("job_id");
            String jobName = set.getString("job_name");
            String jobSalary = set.getString("job_salary");
            String jobEducation = set.getString("job_education");
            String jobExprience = set.getString("job_exprience");
            String jobArea = set.getString("job_area");
            String companyName = set.getString("company_Name");
            JobInfoEntity jobInfo = new JobInfoEntity(jobId,jobName,jobSalary,jobEducation,
                    jobExprience,jobArea,companyName);
            list.add(jobInfo);

        }
        set.close();
        statement.close();
        connection.close();
        return list;
    }

    public void batchInsert(List<JobInfoEntity> list) throws SQLException {
        Connection connection = DBConnection.getConnection();
        String sql = "insert into job_info (job_id,job_name,job_salary," +
                "job_education,job_exprience,job_area,company_Name,search_kw,search_city) " +
                "values (?,?,?,?,?,?,?,?,?)";
        PreparedStatement statement = connection.prepareStatement(sql);
        for(JobInfoEntity jobInfo:list){
            statement.setString(1,jobInfo.getJobId());
            statement.setString(2,jobInfo.getJobName());
            statement.setString(3,jobInfo.getJobSalary());
            statement.setString(4,jobInfo.getJobEducation());
            statement.setString(5,jobInfo.getJobExprience());
            statement.setString(6,jobInfo.getJobArea());
            statement.setString(7,jobInfo.getCompanyName());
            statement.setString(8,jobInfo.getSearchKw());
            statement.setString(9,jobInfo.getSearchCity());
            statement.addBatch();
        }
        statement.executeBatch();
        statement.close();
        connection.close();
    }
}
